/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.DAO;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Estado;
import model.Usuario;

/**
 *
 * @author Rodrigo
 */
public class UsuarioDAO {

    public boolean validarLogin(Usuario user) {
        Connection conn = null;
        try {
            conn = new Conexao().getConexao();

            Statement stm = conn.createStatement();

            ResultSet rs = stm.executeQuery("select * from user where user = '" + user.getLogin() + "' and senha = cast(AES_ENCRYPT('"+user.getSenha()+"',(select pass from aes where id = 1)) as char)");

            if (rs.next()) {
                if (rs.getInt("isNovo") == 1) {
                    user.setNovo(true);
                } else {
                    user.setNovo(false);
                }
                
                //user.setEstado(rs.getString(null));
                user.setOuro(rs.getInt("ouro"));
                user.setId(rs.getInt("id"));

                System.out.println("Usuário logado: " + user.getLogin());
                return true;
            } else {
                user.setErro("<font color = red> Usuário e/ou senha inválido(s). </font>");
                System.out.println("Tentativa de login inválido: " + user.getLogin());
                return false;
            }

        } catch (SQLException ex) {
            Logger.getLogger(UsuarioDAO.class.getName()).log(Level.SEVERE, null, ex);
            user.setErro("<font color = red> "+ex.getMessage()+" </font>");
            return false;
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(UsuarioDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

    }

    public boolean cadastraUsuario(Usuario user) {
        Statement stm = null;
        Connection conn = null;
        try {

            conn = new Conexao().getConexao();
            stm = conn.createStatement();
            stm.execute("insert into user(user, email, senha) values('" + user.getLogin() + "', '" + user.getEmail() + "', AES_ENCRYPT('"+user.getSenha()+"', (select pass from aes where id = 1) ))");
            System.out.println("Usuário cadastrado com sucesso: " + user.getLogin());
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(UsuarioDAO.class.getName()).log(Level.SEVERE, null, ex);
            user.setErro("<font color = red> "+ex.getMessage()+" </font>");
            return false;
        } finally {
            try {
                stm.close();
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(UsuarioDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    
    public void cadastradoEstado(Usuario user, Estado estado) {
        Statement stm = null;
        Connection conn = null;
        try {

            conn = new Conexao().getConexao();
            stm = conn.createStatement();
            
            stm.execute("update user set isNovo = 0, estado = '"+estado.getId()+"', data_alteracao = CURRENT_TIMESTAMP where id = " + user.getId());
            
        } catch (SQLException ex) {
            Logger.getLogger(UsuarioDAO.class.getName()).log(Level.SEVERE, null, ex);

        } finally {
            try {
                stm.close();
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(UsuarioDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    
    public boolean recuperarSenha(Usuario user) {
        Statement stm = null;
        Connection conn = null;
        ResultSet rs = null;
        try {

            conn = new Conexao().getConexao();
            stm = conn.createStatement();
            rs = stm.executeQuery("select cast(AES_DECRYPT(senha,(select pass from aes where id = 1)) as char), user from user where email = '" + user.getEmail() + "'");
            
            if(rs.next()){
                user.setSenha(rs.getString(1));
                user.setLogin(rs.getString(2));
                return true;
            }
            else{
                user.setErro("<font color = red> Email/Usuário digitado não encontrado. </font>");
                return false;
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(UsuarioDAO.class.getName()).log(Level.SEVERE, null, ex);
            user.setErro("<font color = red> "+ex.getMessage()+" </font>");
            return false;

        } finally {
            try {
                stm.close();
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(UsuarioDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}
